﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TIS.DAO
{
    public class SlideShowDAO
    {
        private static readonly String Url = ConfigurationManager.AppSettings["Main.ConnectionString"];
        public SlideShowDAO()
        {

        }

        private SqlConnection _connection;
        private SqlConnection GetConnection()
        {
            _connection = new SqlConnection(Url);
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            return _connection;
        }

        public DataTable GetSlideShowAll(int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@langId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM SlideShow WHERE LanguageId = @langId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }
        public DataTable GetSlideShow(int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@langId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM SlideShow WHERE LanguageId = @langId AND SlideShowStatus = 1 ORDER BY SlideShowPosition", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }
        public DataTable GetSlideShow(int id,int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@LanguageId", langId);
                spr[1] = new SqlParameter("@SlideShowId", id);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM SlideShow WHERE LanguageId = @LanguageId AND SlideShowId = @SlideShowId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public void DeleteSlideShow(int id, int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@LanguageId", langId);
                spr[1] = new SqlParameter("@SlideShowId", id);
                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text, "DELETE FROM SlideShow WHERE LanguageId = @LanguageId AND SlideShowId = @SlideShowId", spr);

            }
            finally
            {
                _connection.Close();
            }
        }
        public void InsertSlideShow(string filePath, int position, string url, int langId, int status)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[5];
                spr[0] = new SqlParameter("@SlideShowName", filePath);
                spr[1] = new SqlParameter("@SlideShowPosition", position);
                spr[2] = new SqlParameter("@SlideShowURL", url);
                spr[3] = new SqlParameter("@LanguageId", langId);
                spr[4] = new SqlParameter("@SlideShowStatus", status);

                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                    "INSERT SlideShow(SlideShowName,SlideShowPosition,SlideShowURL,LanguageId,SlideShowStatus) VALUES(@SlideShowName,@SlideShowPosition,@SlideShowURL,@LanguageId,@SlideShowStatus)", spr);

            }
            finally
            {
                _connection.Close();
            }
        }
        public void UpdateSlideShow(int id, string filePath, int position, string url, int langId, int status)
        {
            try
            {
                _connection = GetConnection();
                if (filePath != "")
                {
                    SqlParameter[] spr = new SqlParameter[6];
                    spr[0] = new SqlParameter("@SlideShowName", filePath);
                    spr[1] = new SqlParameter("@SlideShowPosition", position);
                    spr[2] = new SqlParameter("@SlideShowURL", url);
                    spr[3] = new SqlParameter("@LanguageId", langId);
                    spr[4] = new SqlParameter("@SlideShowStatus", status);
                    spr[5] = new SqlParameter("@SlideShowId", id);
                    SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                        "UPDATE SlideShow SET SlideShowName = @SlideShowName,SlideShowPosition = @SlideShowPosition,SlideShowURL = @SlideShowURL,LanguageId = @LanguageId,SlideShowStatus = @SlideShowStatus WHERE SlideShowId = @SlideShowId", spr);
                }
                else
                {
                    SqlParameter[] spr = new SqlParameter[5];
                    spr[0] = new SqlParameter("@SlideShowPosition", position);
                    spr[1] = new SqlParameter("@SlideShowURL", url);
                    spr[2] = new SqlParameter("@LanguageId", langId);
                    spr[3] = new SqlParameter("@SlideShowStatus", status);
                    spr[4] = new SqlParameter("@SlideShowId", id);
                    SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                        "UPDATE SlideShow SET SlideShowPosition = @SlideShowPosition,SlideShowURL = @SlideShowURL,LanguageId = @LanguageId,SlideShowStatus = @SlideShowStatus WHERE SlideShowId = @SlideShowId", spr);
                }


            }
            finally
            {
                _connection.Close();
            }
        }

    }
}